1. pymysql的介绍
- 作用: 用于变成原生SQL语句的模块
2. pymysql 和 MySQLdb 的区别
- pymysql 和 MySQLdb 的语法和用法都是一样的，只是所导入的模块名不一样
- pymysql 支持 python2、3
- MySQLdb 支持 python2
3. pymysql的安装
pip3 install pymysql -i https://pypi.douban.com/simple # 使用豆瓣的镜像
4. pymysql的基本使用
import pymysql
username = input('用户名：')
password = input('密码：')
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8') # 连接数据库
cursor = conn.cursor() # 游标: 操作数据
sql = 'select * from userinfo where username=%s and password=%s'
cursor.execute(sql, [username, password]) # 执行sql语句
result = cursor.fetchone() # 获取查询到的数据中的一条数据
print(result) # 返回结果: (1, 'Kevin', '123')
print('登录成功' if result else '登录失败')
cursor.close() # 关闭游标
conn.close() # 关闭对数据库的连接
5. 防止SQL注入
- 不要在sql语句后面直接使用 % 拼接上变量名，而是将所要拼接的变量放入数组，元祖或字典中变成可迭代对象传入到 cursor.execute(sql, 变量名的可迭代对象) 当中，而这么做就是为了防止sql注入
- 写法一
import pymysql
username = input('用户名：')
password = input('密码：')
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = 'select * from userinfo where username=%s and password=%s'
cursor.execute(sql, [username, password]) # 执行sql语句
result = cursor.fetchone()
print(result)
print('登录成功' if result else '登录失败')
cursor.close()
conn.close()
- 写法二
import pymysql
username = input('用户名：')
password = input('密码：')
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = 'select * from userinfo where username=%(username)s and password=%(password)s'
cursor.execute(sql, {'username': username, 'password': password}) # 执行sql语句
result = cursor.fetchone()
print(result)
print('登录成功' if result else '登录失败')
cursor.close()
conn.close()
- 错误示范
import pymysql
username = input('用户名：') # xxx' or 1=1 --
password = input('密码：') # 123
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = "select * from userinfo where username='%s' and password='%s'" % (username, password) # 不要在sql语句后面直接使用 % 拼接上变量名
'''
sql注入:
如果此时 username 输入了 xxx' or 1=1 -- (-- 在sql语句中代表注释，且 -- 后面一定要带上空格),
sql 语句就会变成 select * from userinfo where username='xxx' or 1=1 -- ' and password='123'
'''
print(sql) # select * from userinfo where username='xxx' or 1=1 -- ' and password='123'
cursor.execute(sql)
result = cursor.fetchone()
print(result) # 返回结果: (1, 'Kevin', '123')
print('登录成功' if result else '登录失败')
cursor.close()
conn.close()
6. cursor.execute(sql, 可迭代对象) -> 使用方法
- 数据形式的可迭代对象
import pymysql
username = input('用户名：')
password = input('密码：')
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = 'select * from userinfo where username=%s and password=%s'
cursor.execute(sql, [username, password]) # 执行sql语句 -> cursor.execute(sql, 数据形式的可迭代对象)
result = cursor.fetchone()
print(result)
print('登录成功' if result else '登录失败')
cursor.close()
conn.close()
- 对象形式的可迭代对象
import pymysql
username = input('用户名：')
password = input('密码：')
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = 'select * from userinfo where username=%(u)s and password=%(p)s'
cursor.execute(sql, {'u': username, 'p': password}) # 执行sql语句 -> cursor.execute(sql, 对象形式的可迭代对象) -> 如果使用了对象形式的可迭代对象，那么sql语句的格式化输出也要进行修改
result = cursor.fetchone()
print(result)
print('登录成功' if result else '登录失败')
cursor.close()
conn.close()
7. 查询数据
- .fetchone()
- 获取查询到的数据中的一条数据
- .fetchone() 有点类似于 next() 方法，如果执行多次 .fetchone() 方法，每一次都会接着上一次的位置继续获取
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = 'select * from userinfo'
cursor.execute(sql)
result1 = cursor.fetchone()
print(result1) # (1, 'Kevin', '123')
result2 = cursor.fetchone()
print(result2) # (2, 'Yeung', '123')
result3 = cursor.fetchone()
print(result3) # (3, 'Jack', '123')
result4 = cursor.fetchone()
print(result4) # (4, 'Timmy', '123')
cursor.close()
conn.close()
- .fetchmany(num)
- 获取查询到的数据中的num条数据
- .fetchmany(num) 有点类似于 next() 方法，如果执行多次 .fetchmany(num) 方法，每一次都会接着上一次的位置继续获取
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = 'select * from userinfo'
cursor.execute(sql)
result1 = cursor.fetchmany(2)
print(result1) # ((1, 'Kevin', '123'), (2, 'Yeung', '123'))
result2 = cursor.fetchmany(2)
print(result2) # ((3, 'Jack', '123'), (4, 'Timmy', '123'))
cursor.close()
conn.close()
- .fetchall()
- 获取查询到的数据中的全部数据
- 如果 .fetchall() 上面还执行了 .fetchone() 或 .fetchmany(num) 那么它就会接着上一次的位置获取剩余的数据 -> 下面有相应的例子
- 在实现分页功能的时候不要将所有数据一次性查询出来后通过 .fetchall() 获取查询到的数据，然后在进行分页的操作，而是使用 sql 进行分页查询，最后通过 .fetchall() / .fetchone() / .fetchmany(num) 获取查询到的数据然后再进行处理
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = 'select * from userinfo'
cursor.execute(sql)
result = cursor.fetchall()
print(result) # ((1, 'Kevin', '123'), (2, 'Yeung', '123'), (3, 'Jack', '123'), (4, 'Timmy', '123'))
cursor.close()
conn.close()
- .cursor(cursor=pymysql.cursors.DictCursor) -> 将查询到的数据以字典的形式展示
- 默认情况下，我们获取查询到的数据一般是以元组的形式展示出来，只能看到每行的数据，却不知道每一列代表的是什么，这个时候可以使用以下方式来返回字典，每一行的数据都会生成一个字典
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 将查询到的数据以字典的形式展示
sql = 'select * from userinfo'
cursor.execute(sql)
result1 = cursor.fetchone()
print(result1) # {'id': 1, 'username': 'Kevin', 'password': '123'}
result2 = cursor.fetchmany(2)
print(result2) # [{'id': 2, 'username': 'Yeung', 'password': '123'}, {'id': 3, 'username': 'Jack', 'password': '123'}]
result3 = cursor.fetchall()
print(result3) # [{'id': 4, 'username': 'Timmy', 'password': '123'}]
cursor.close()
conn.close()
8.添加/修改/删除数据 -> 这里只使用添加数据做说明，因为修改和删除的用法都是一样的只是SQL语句不一样
- 注意事项:
- 在进行添加/修改/删除数据的时候一定执行 .commit() 方法 将数据提交到数据库，不然会没有效果
- .execute(sql语句, 可迭代对象) 方法的返回值是受影响的行数
- 添加一条数据
import pymysql
username = 'Aimer'
password = '123'
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = 'insert into userinfo(username,password) values (%s,%s)'
result = cursor.execute(sql, [username, password]) # 执行sql语句，返回受影响的行数，可以不用接受返回值直接执行 execute() 方法（这里这么做只是为了查看返回值是什么）
print(result) # 1
conn.commit() # 事务: 提交数据到数据库 -> 一定执行 .commit() 方法 将数据提交到数据库，不然会没有效果
conn.close()
cursor.close()
- .executemany(sql语句,[可迭代对象, 可迭代对象, ……]) -> executemany一般用于批量添加数据，批量删除和修改数据都可以通过SQL语句实现 -> 批量添加数据
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = 'insert into userinfo(username,password) values (%s,%s)'
result = cursor.executemany(sql, [('凯文', 123), ('张三', 123), ('李四', 123), ('王五', 123)]) # 执行sql语句，返回受影响的行数，可以不用接受返回值直接执行 executemany() 方法（这里这么做只是为了查看返回值是什么）
print(result) # 4
conn.commit() # 事务: 提交数据到数据库 -> 一定执行 .commit() 方法 将数据提交到数据库，不然会没有效果
conn.close()
cursor.close()
- .lastrowid -> 获取新插入数据的自增id
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = 'insert into userinfo(username,password) values (%s,%s)'
cursor.execute(sql, ['狗蛋', 123])
print(cursor.lastrowid) # 14 -> 获取新插入数据的自增id
conn.commit()
conn.close()
cursor.close()
- 注意事项: 如果使用 .lastrowid 去获取批量添加数据的自增id，那么 .lastrowid 只会获取到批量添加的第一条数据的自增id
import pymysql
conn = pymysql.connect(host='localhost', user='root', password='', database='db1', charset='utf8')
cursor = conn.cursor()
sql = 'insert into userinfo(username,password) values (%s,%s)'
cursor.executemany(sql, [('Eric', 123), ('Amy', 123), ('Tony', 123)])
print(cursor.lastrowid) # 15 -> 如果使用 .lastrowid 去获取批量添加数据的自增id，那么 .lastrowid 只会获取到批量添加的第一条数据的自增id
conn.commit()
conn.close()
cursor.close()
9.调用存储过程
- .callproc('存储过程名称', 可迭代对象) -> 将要传递的参数放进可迭代对象里
import pymysql
conn = pymysql.connect(host="localhost", user='root', password='', database="db2", charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc('p1', (5,)) # 调用名为 p1 的存储过程
conn.commit()
result = cursor.fetchall() # 获取存储过程的结果集
print(result) # [{'id': 1, 'name': 'Kevin', 'age': 18, 'sex': '男'}, {'id': 2, 'name': 'Yeung', 'age': 23, 'sex': '男'}]
cursor.close()
conn.close()
# sql语句 -> 存储过程
delimiter //
create procedure p1 (
in d1 int
)
begin
select * from t1 where id < d1;
end //
delimiter ;
- 获取存储过程的返回值
- 获取存储过程的返回值需要再进行一次 SQL 语句查询才能获取到 -> cursor.execute('select @_存储过程名称_1, @_存储过程名称_2, @_存储过程名称_3, ……')
import pymysql
conn = pymysql.connect(host="localhost", user='root', password='', database="db2", charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc('p1', (3, 2, 3)) # 调用名为 p1 的存储过程
conn.commit()
result1 = cursor.fetchall() # 获取存储过程的结果集
print(result1) # [{'id': 1, 'name': 'Kevin', 'age': 18, 'sex': '男'}, {'id': 2, 'name': 'Yeung', 'age': 23, 'sex': '男'}]
cursor.execute('select @_p1_1, @_p1_2') # 获取存储过程的返回值需要再进行一次 SQL 语句查询才能获取到
result2 = cursor.fetchall()
print(result2) # [{'@_p1_1': 20, '@_p1_2': 33}]
cursor.close()
conn.close()
# sql语句 -> 存储过程
delimiter //
create procedure p1 (
in d1 int,
out d2 int,
inout d3 int
)
begin
select * from t1 where id < d1;
set d2 = 20;
set d3 = d3 + 30;
end //
delimiter ;
← pipreqs 模块 queue 队列模块 →